package net.noday.demo.persistent.jdbcImpl;

import java.util.List;

import javax.sql.DataSource;

import net.noday.demo.domain.Article;
import net.noday.demo.domain.mapping.ArticleMapping;
import net.noday.demo.persistent.jdbcImpl.mapper.ArticleMapper;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

@Repository("articleDAO")//
public class JdbcArticleDAO {

	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public Article loadById(long id) {
		return jdbcTemplate.queryForObject(
				"select * from " + ArticleMapping.TABLENAME + " where id = ?", 
				new Object[]{id}, new ArticleMapper());
	}

	public Article loadObject(String sql, Object[] param) {
		return jdbcTemplate.queryForObject(sql, param, new ArticleMapper());
	}

	public int delById(long id) {
		return jdbcTemplate.update("delete from " + ArticleMapping.TABLENAME + " where id = ?", 
				new Object[]{id}, new int[]{java.sql.Types.INTEGER});
	}

	public Article save(Article article) {
		SqlParameterSource parameters = new BeanPropertySqlParameterSource(article);
		Number id = new SimpleJdbcInsert(jdbcTemplate)
			.withTableName(ArticleMapping.TABLENAME)
			.usingGeneratedKeyColumns(ArticleMapping.ID)
			.executeAndReturnKey(parameters);
		article.setId(id.intValue());
		return article;
	}
	public int update(Article article) {
		String sql = "update " + ArticleMapping.TABLENAME + " set " + 
		ArticleMapping.TITLE + "=?," + ArticleMapping.SUMMARY + "=?," + 
		ArticleMapping.CONTENT + "=?," + ArticleMapping.CREATEDATE + "=?," + 
		ArticleMapping.UPDATEDATE + "=?," + ArticleMapping.TAGS + "=?," + 
		ArticleMapping.COMMENTCOUNT + "=?," + ArticleMapping.KEYWORD + "=?," + 
		ArticleMapping.LEVEL + "=?," + "where " + ArticleMapping.ID + "=?";
		return jdbcTemplate.update(sql, article.getTitle(), article.getSummary(), 
				article.getContent(), article.getCreateDate(), 
				article.getUpdateDate(), article.getTags(), 
				article.getCommentCount(), article.getKeyword(), 
				article.getLevel(), article.getId());
		
	}

	public List<Article> listAll() {
		String sql = "select * from " + ArticleMapping.TABLENAME + " order by id";
		return jdbcTemplate.query(sql, new ArticleMapper());
	}

	/**
	 * 分页查询 升序
	 * @param params 向后翻页1.">", 2.当前页最大id, 3."ASC", 4.(翻页数-1)*每页数量, 5.每页数量
	 * @param params 向前翻页1."<", 2.当前页最小id, 3."DESC", 4.(翻页数-1)*每页数量, 5.每页数量
	 * @return
	 */
	public List<Article> listPageByASC(Object[] params) {
		String sql = "(select * from " + ArticleMapping.TABLENAME + " where id" + 
			params[0] + "? order by id " + params[2] + " limit ?,?) order by id asc";
		return jdbcTemplate.query(sql, new ArticleMapper(), params[1], params[3], params[4]);
	}
	public List<Article> listPageByDESC(Object[] params) {
		String sql = "(select * from " + ArticleMapping.TABLENAME + " where id" + 
		params[0] + "? order by id " + params[2] + " limit ?,?) order by id desc";
		return jdbcTemplate.query(sql, new ArticleMapper(), params[1], params[3], params[4]);
	}
	
	public List<Article> query(String sql) {
		return jdbcTemplate.query(sql, new ArticleMapper());
	}
	
	public List<Article> query(String sql, Object[] params) {
		return jdbcTemplate.query(sql, params, new ArticleMapper());
	}

	public int countAll() {
		return jdbcTemplate.queryForInt("select count(*) from " + ArticleMapping.TABLENAME);
	}

	public int countQuery(String sql) {
		return jdbcTemplate.queryForInt(sql);
	}

	public int update(String hql) {
		// TODO Auto-generated method stub
		return 0;
	}

}
